import sqlite3
from datetime import datetime
from database.db_manager import get_connection

def add_event(title, description, event_time, reminder_time, category=None, recurring_type=None, recurring_end_date=None):
    """添加新日程（返回ID）"""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            """
            INSERT INTO events 
            (title, description, event_time, reminder_time, category, recurring_type, recurring_end_date)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            """,
            (title, description, event_time, reminder_time, category, recurring_type, recurring_end_date)
        )
        conn.commit()
        return cursor.lastrowid
    except sqlite3.Error as e:
        print(f"[DB ERROR] Add event failed: {e}")
        raise
    finally:
        conn.close()

def get_events():
    """获取所有日程（按时间排序）"""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT id, title, description, event_time, reminder_time, is_reminded, 
                   category, recurring_type, recurring_end_date
            FROM events
            ORDER BY event_time
        """)
        return cursor.fetchall()
    except sqlite3.Error as e:
        print(f"[DB ERROR] Get events failed: {e}")
        raise
    finally:
        conn.close()

def get_upcoming_events():
    """获取即将提醒的日程（reminder_time <= 当前时间）"""
    now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT id, title, description, event_time, reminder_time, is_reminded, 
                   category, recurring_type, recurring_end_date
            FROM events
            WHERE reminder_time <= ? AND is_reminded = 0
        """, (now,))
        return cursor.fetchall()
    except sqlite3.Error as e:
        print(f"[DB ERROR] Get upcoming events failed: {e}")
        raise
    finally:
        conn.close()

def update_reminded_flag(event_id):
    """标记日程为已提醒"""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            "UPDATE events SET is_reminded = 1 WHERE id = ?",
            (event_id,)
        )
        conn.commit()
    except sqlite3.Error as e:
        print(f"[DB ERROR] Update reminded flag failed: {e}")
        raise
    finally:
        conn.close()

def get_events_by_category(category):
    """按分类获取日程"""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT id, title, description, event_time, reminder_time, is_reminded, 
                   category, recurring_type, recurring_end_date
            FROM events
            WHERE category = ?
            ORDER BY event_time
        """, (category,))
        return cursor.fetchall()
    except sqlite3.Error as e:
        print(f"[DB ERROR] Get by category failed: {e}")
        raise
    finally:
        conn.close()


def delete_event(event_id):
    """删除指定ID的日程"""
    conn = get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM events WHERE id = ?", (event_id,))
        conn.commit()
    except sqlite3.Error as e:
        print(f"[DB ERROR] Delete event failed: {e}")
        raise
    finally:
        conn.close()